﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
/// DbAccess 的摘要说明
/// </summary>
public class DbAccess
{
    private static SqlConnection conn;
    private SqlDataAdapter da;
    private DataSet ds;
    private SqlCommand cmd;
	public DbAccess()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
    /// <summary>
    /// 获取数据库连接
    /// </summary>
    /// <returns>获取数据库连接</returns>
    internal static SqlConnection GetConn()
    {
        try
        {
            //从webConfig中读取数据库链接字符串
            string cstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            conn = new SqlConnection(cstr);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();

            }
            return conn;
        }
        catch (Exception e)
        {      
        }
        return null;
    }

   /// <summary>
   /// 关闭数据库链接
   /// </summary>
    internal static void CloseConn()
    {
        if (conn.State != ConnectionState.Closed)
        {
            conn.Close();
        }
    }

  /// <summary>
  /// 执行Sql语句，包括添加，删除，和修改语句
  /// </summary>
  /// <param name="sql">sql语句</param>
  /// <returns>数据库影响的行数</returns>
    public bool ExecuteSql(string sql)
    {
        try
        {
            conn = GetConn();
           
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            } 
            cmd = new SqlCommand(sql, conn);
            int flag = cmd.ExecuteNonQuery();

            return flag > 0;
        }
        catch (Exception e)
        { 
        }
        finally
        {
            conn.Close();

        }
        return false;
    }
    

    /// <summary>
    /// 根据sql语句返回DataSet类型数据集
    /// </summary>
    /// <param name="sql">Sql查询语句</param>
    /// <returns>DataSet数据集</returns>
    public DataSet GetDataSet(string sql)
    {
        try
        {
            conn = GetConn();
            da = new SqlDataAdapter(sql, conn);
            DataSet dst = new DataSet();
            da.Fill(dst);

            return dst;
        }
        catch { }
        finally
        {
            conn.Close();
        }
        return null;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="dt">购物车的datable,guid为会话ID，uid为用户名</param>
    /// <returns></returns>
    public bool DtbAdd(DataTable dt,string guid,string uid)
    {
        try
        {
            string sql = "";
            conn = DbAccess.GetConn();
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            DataSet dl = new DataSet();
            int Leave = 0;//剩余数量
            int buy = 0;//购买数量
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ////获取剩余数量
                sql = "select ProNum from product where ProId=" + dt.Rows[i]["ProId"].ToString() + "";
                da = new SqlDataAdapter(sql, conn);
                da.Fill(dl);
                if (int.Parse(dt.Rows[i]["ProNum"].ToString()) <= int.Parse(dl.Tables[0].Rows[0][0].ToString()))
                {
                    Leave = int.Parse(dl.Tables[0].Rows[0][0].ToString()) - int.Parse(dt.Rows[i]["ProNum"].ToString());
                    buy = int.Parse(dt.Rows[i]["ProNum"].ToString());
                }
                else
                {
                    Leave = 0;
                    buy = int.Parse(dl.Tables[0].Rows[0][0].ToString());


                }
                dl.Clear();
                sql = "insert into OrderDetail values('" + dt.Rows[i]["ProId"].ToString() + "','" + dt.Rows[i]["ProName"].ToString() + "'," + decimal.Parse(dt.Rows[i]["MemberPrice"].ToString()) + "," + buy + ",'" + guid + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";
                sql = sql + "\n" + "update Product set ProNum=" + Leave + " where ProId=" + dt.Rows[i]["ProId"].ToString() + "";
                cmd = new SqlCommand(sql, conn);
                int flag = cmd.ExecuteNonQuery();
                if (flag > 0)
                    continue;
                else
                    break;

            }
            return true;
        }
        catch (Exception ep)
        {
            return false;
        }
        finally {
            conn.Close();
        }
 return false;
    
    }
    /// <summary>
    /// 分页
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="start">开始索引</param>
    /// <param name="pagesize">页面大小</param>
    /// <returns>数据集合</returns>
    public DataSet getPage(string sql, int start, int pagesize)
    {
        try
        {
            
            conn = GetConn();
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds, start, pagesize, "Product");//
            return ds;

        }
        catch
        {

        }
        finally
        {
            conn.Close();
        }
        return null;
    }
}
